package org.skydiveapplibrary.repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;

import org.skydiveapplibrary.data.Aircraft;
import org.skydiveapplibrary.data.AltitudeUnit;
import org.skydiveapplibrary.data.Database;
import org.skydiveapplibrary.data.Location;
import org.skydiveapplibrary.data.LogEntry;
import org.skydiveapplibrary.data.Rig;
import org.skydiveapplibrary.data.Settings;
import org.skydiveapplibrary.data.SkydiveType;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class LogEntryRepository
{
	// custom column names
	public static final String SKYDIVE_TYPE_NAME = "skydive_type_name";
	public static final String LOCATION_NAME = "location_name";
	public static final String AIRCRAFT_NAME = "aircraft_name";
	public static final String SIGNATURE_ID = "signature_id";
	
	// list query
	private static final String GET_LOG_ENTRIES_QUERY =
		"select " +
		"log_entry._id, " +
		"log_entry.jump_number, " +
		"log_entry.date, " +
		"log_entry.notes, " +
		"skydive_type.name as skydive_type_name, " +
		"location.name as location_name, " +
		"aircraft.name as aircraft_name, " +
		"log_entry_signature._id as signature_id " +
		"from log_entry " +
		"left outer join skydive_type on log_entry.skydive_type = skydive_type._id " +
		"left outer join location on log_entry.location = location._id " +
		"left outer join aircraft on log_entry.aircraft = aircraft._id " +
		"left outer join log_entry_signature on log_entry._id = log_entry_signature.log_entry " +
		"order by log_entry.jump_number desc";
	
	// export query
	private static final String GET_LOG_ENTRIES_EXPORT_QUERY =
		"select " +
		"log_entry._id, " +
		"log_entry.jump_number, " +
		"log_entry.date, " +
		"log_entry.altitude_unit, " +
		"log_entry.exit_altitude, " +
		"log_entry.deployment_altitude, " +
		"log_entry.freefall_time_seconds, " +
		"skydive_type.unique_id as skydive_type, " +
		"location.unique_id as location, " +
		"aircraft.unique_id as aircraft, " +
		"log_entry.cutaway, " +
		"log_entry.diagram, " +
		"log_entry.notes, " +
		"log_entry.last_modified_utc, " +
		"log_entry.last_signature_utc, " +
		"log_entry.unique_id " +
		"from log_entry " +
		"left outer join skydive_type on log_entry.skydive_type = skydive_type._id " +
		"left outer join location on log_entry.location = location._id " +
		"left outer join aircraft on log_entry.aircraft = aircraft._id";
	
	// export query
	private static final String GET_LOG_ENTRIES_RIGS_EXPORT_QUERY =
			"select " +
			"log_entry_rig._id, " +
			"log_entry_rig.log_entry, " +
			"rig.unique_id as rig " +
			"from log_entry_rig " +
			"left outer join rig on log_entry_rig.rig = rig._id";
	
	private Database database;
	private SettingsRepository settingsRepository;
	private SkydiveTypeRepository skydiveTypeRepository;
	private LocationRepository locationRepository;
	private AircraftRepository aircraftRepository;
	private RigRepository rigRepository;
	private SignatureRepository signatureRepository;
	
	public LogEntryRepository(Context context)
	{
		database = new Database(context);
		settingsRepository = new SettingsRepository(context);
		skydiveTypeRepository = new SkydiveTypeRepository(context);
		locationRepository = new LocationRepository(context);
		aircraftRepository = new AircraftRepository(context);
		rigRepository = new RigRepository(context);
		signatureRepository = new SignatureRepository(context);
	}
	
	public Cursor getLogEntryCursor()
	{
		// activity expected to manage cursor
		SQLiteDatabase db = database.getReadableDatabase();
		// query
		return db.rawQuery(GET_LOG_ENTRIES_QUERY, null);
	}
	
	public Cursor getAllLogEntryCursor()
	{
		// activity expected to manage cursor
		SQLiteDatabase db = database.getReadableDatabase();
		// query
		return db.query(
				Database.LOG_ENTRY_TABLE,
				Database.LOG_ENTRY_ALL_COLUMNS,
				null, null, null, null, null);
	}
	
	public Cursor getAllLogEntryForExportCursor()
	{
		// activity expected to manage cursor
		SQLiteDatabase db = database.getReadableDatabase();
		// query
		return db.rawQuery(GET_LOG_ENTRIES_EXPORT_QUERY, null);
	}
	
	public Cursor getAllLogEntryRigsCursor()
	{
		// activity expected to manage cursor
		SQLiteDatabase db = database.getReadableDatabase();
		// query
		return db.query(
				Database.LOG_ENTRY_RIG_TABLE,
				Database.LOG_ENTRY_RIG_ALL_COLUMNS,
				null, null, null, null, null);
	}
	
	public Cursor getAllLogEntryRigsForExportCursor()
	{
		// activity expected to manage cursor
		SQLiteDatabase db = database.getReadableDatabase();
		// query
		return db.rawQuery(GET_LOG_ENTRIES_RIGS_EXPORT_QUERY, null);
	}
	
	public Cursor getAllLogEntrySignaturesCursor()
	{
		// activity expected to manage cursor
		SQLiteDatabase db = database.getReadableDatabase();
		// query
		return db.query(
				Database.LOG_ENTRY_SIGNATURE_TABLE,
				Database.LOG_ENTRY_SIGNATURE_ALL_COLUMNS,
				null, null, null, null, null);
	}
	
	public LogEntry getLogEntry(int id)
	{
		LogEntry entry = null;
		
		SQLiteDatabase db = database.getReadableDatabase();
		Cursor cursor = null;
		try
		{
			cursor = db.query(
					Database.LOG_ENTRY_TABLE,
					Database.LOG_ENTRY_ALL_COLUMNS,
					Database.LOG_ENTRY_ID + " = " + id,
					null, null, null, null);
			
			if (cursor.moveToFirst())
			{
				entry = createLogEntry(cursor);
				
				// get skydive type
				if (!Database.isNull(cursor, Database.LOG_ENTRY_SKYDIVE_TYPE))
				{
					int typeId = Database.getInteger(cursor, Database.LOG_ENTRY_SKYDIVE_TYPE);
					SkydiveType skydiveType = skydiveTypeRepository.getSkydiveType(typeId);
					entry.setSkydiveType(skydiveType);
				}
				// get location
				if (!Database.isNull(cursor, Database.LOG_ENTRY_LOCATION))
				{
					int locId = Database.getInteger(cursor, Database.LOG_ENTRY_LOCATION);
					Location location = locationRepository.getLocation(locId);
					entry.setLocation(location);
				}
				// get aircraft
				if (!Database.isNull(cursor, Database.LOG_ENTRY_AIRCRAFT))
				{
					int aircraftId = Database.getInteger(cursor, Database.LOG_ENTRY_AIRCRAFT);
					Aircraft aircraft = aircraftRepository.getAircraft(aircraftId);
					entry.setAircraft(aircraft);
				}
				// get rigs
				entry.getRigs().addAll(
						getRigs(entry.getId(), db));
				// get signature
				entry.setSignature(signatureRepository.getSignatureForLogEntry(id));
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
			// close db
			db.close();
		}
		
		return entry;
	}
	
	public boolean exists(int jumpNumber)
	{
		SQLiteDatabase db = database.getReadableDatabase();
		Cursor cursor = null;
		try
		{
			cursor = db.query(
					Database.LOG_ENTRY_TABLE,
					Database.LOG_ENTRY_ALL_COLUMNS,
					Database.LOG_ENTRY_JUMP_NUMBER + " = " + jumpNumber,
					null, null, null, null);
			return cursor.getCount() > 0;
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
			// close db
			db.close();
		}
	}
	
	public boolean uniqueIdExists(String uniqueId)
	{
		SQLiteDatabase db = database.getReadableDatabase();
		Cursor cursor = null;
		try
		{
			cursor = db.query(
					Database.LOG_ENTRY_TABLE,
					Database.LOG_ENTRY_ALL_COLUMNS,
					Database.LOG_ENTRY_UNIQUE_ID + " = '" + uniqueId + "'",
					null, null, null, null);
			return cursor.getCount() > 0;
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
			// close db
			db.close();
		}
	}
	
	public int getIdFromJumpNumber(int jumpNumber)
	{
		SQLiteDatabase db = database.getReadableDatabase();
		Cursor cursor = null;
		Integer logEntryId = 0;
		try
		{
			cursor = db.query(
					Database.LOG_ENTRY_TABLE,
					Database.LOG_ENTRY_ALL_COLUMNS,
					Database.LOG_ENTRY_JUMP_NUMBER + " = " + jumpNumber,
					null, null, null, null);
			
			if (cursor.moveToFirst())
			{
				if (!Database.isNull(cursor, Database.LOG_ENTRY_ID))
				{
					logEntryId = Database.getInteger(cursor, Database.LOG_ENTRY_ID);
				}
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
			// close db
			db.close();
		}		
		return logEntryId;
	}
	
	public int getIdFromUniqueId(String uniqueId)
	{
		SQLiteDatabase db = database.getReadableDatabase();
		Cursor cursor = null;
		Integer logEntryId = 0;
		try
		{
			cursor = db.query(
					Database.LOG_ENTRY_TABLE,
					Database.LOG_ENTRY_ALL_COLUMNS,
					Database.LOG_ENTRY_UNIQUE_ID + " = '" + uniqueId + "'",
					null, null, null, null);
			
			if (cursor.moveToFirst())
			{
				if (!Database.isNull(cursor, Database.LOG_ENTRY_ID))
				{
					logEntryId = Database.getInteger(cursor, Database.LOG_ENTRY_ID);
				}
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
			// close db
			db.close();
		}		
		return logEntryId;
	}
	
	public void setDefaults(LogEntry entry)
	{
		// default to 1
		entry.setJumpNumber(1);
		// default to today
		entry.setDate(new Date());
		
		// use default settings
		Settings settings = settingsRepository.getSettings();
		entry.setAltitudeUnit(settings.getAltitudeUnit());
		entry.setExitAltitude(settings.getExitAltitude());
		entry.setDeploymentAltitude(settings.getDeploymentAltitude());
		entry.setFreeFallTimeSeconds(0);
		entry.setLastModifiedUtc(new Date(System.currentTimeMillis()));
		
		// set default location/rigs
		entry.setLocation(locationRepository.getHomeLocation());
		List<Rig> rigs = rigRepository.getPrimaryRigs();
		if (rigs.size() >= 0)
		{
			entry.getRigs().addAll(rigs);
		}
		
		SQLiteDatabase db = database.getReadableDatabase();
		Cursor cursor = null;
		try
		{
			// get max values from db
			cursor = db.query(
					Database.LOG_ENTRY_TABLE,
					new String[]
					{
						Database.max(Database.LOG_ENTRY_JUMP_NUMBER) + " as " + Database.LOG_ENTRY_JUMP_NUMBER
					},
					null, null, null, null, null);
			
			if (cursor.moveToFirst())
			{
				// one more than last
				if (!Database.isNull(cursor, Database.LOG_ENTRY_JUMP_NUMBER))
				{
					entry.setJumpNumber(Database.getInteger(cursor, Database.LOG_ENTRY_JUMP_NUMBER) + 1);
				}
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
			// close db
			db.close();
		}
	}

	public LogEntry getNewFromLastEntry()
	{	
		Integer lastEntryID = -1;
		Integer lastJumpNumber = -1;
		LogEntry entry;
		
		SQLiteDatabase db = database.getReadableDatabase();
		Cursor cursor = null;
		try
		{
			// get max jump # from db
			cursor = db.query(
					Database.LOG_ENTRY_TABLE,
					new String[] { Database.max(Database.LOG_ENTRY_JUMP_NUMBER) + " as " + Database.LOG_ENTRY_JUMP_NUMBER, },
					null, null, null, null, null);
			// get max jump # result
			if (cursor.moveToFirst())
			{
				if (!Database.isNull(cursor, Database.LOG_ENTRY_JUMP_NUMBER))
				{
					lastJumpNumber = Database.getInteger(cursor, Database.LOG_ENTRY_JUMP_NUMBER);
				}
			}
			
			// close cursor
			cursor.close();
			
			if (lastJumpNumber > 0)
			{
				// get id for last jump #
				cursor = db.query(
						Database.LOG_ENTRY_TABLE,
						new String[] { Database.LOG_ENTRY_ID },
						Database.LOG_ENTRY_JUMP_NUMBER + " = " + lastJumpNumber,
						null, null, null, null);
				// get id result
				if (cursor.moveToFirst())
				{
					if (!Database.isNull(cursor, Database.LOG_ENTRY_ID))
					{
						lastEntryID = Database.getInteger(cursor, Database.LOG_ENTRY_ID);
					}
				}
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
			// close db
			db.close();
		}

		if (lastEntryID >= 0)
		{
			entry = this.getLogEntry(lastEntryID);
			entry.setJumpNumber(lastJumpNumber+1);
			entry.setUniqueId(UUID.randomUUID().toString());
			entry.setLastModifiedUtc(new Date(System.currentTimeMillis()));
			entry.setSignature(null);
		}
		else
		{
			entry = new LogEntry();
			this.setDefaults(entry);
		}
		
		return entry;
	}
	
	public int addLogEntry(LogEntry entry)
	{
		// set modified
		if (entry.getLastModifiedUtc() == null)
		{
			entry.setLastModifiedUtc(new Date(System.currentTimeMillis()));
		}
		
		// set uniqueId
		if (entry.getUniqueId() == null || entry.getUniqueId() == "")
		{
			entry.setUniqueId(UUID.randomUUID().toString());
		}
		
		// get insert values
		ContentValues values = createContentValues(entry);
		
		SQLiteDatabase db = database.getWritableDatabase();
		// start tx
		db.beginTransaction();
		try
		{
			// insert log entry
			long logEntryId = db.insert(Database.LOG_ENTRY_TABLE, null, values);
			
			// add rigs
			addRigs((int)logEntryId, entry.getRigs(), db);
			
			// set tx success
			db.setTransactionSuccessful();
			
			return (int)logEntryId;
		}
		finally
		{
			// end tx
			db.endTransaction();
			// close db
			db.close();
		}
	}
	
	public void updateLogEntry(LogEntry entry)
	{
		updateLogEntry(entry, true);
	}
	
	public void updateLogEntry(LogEntry entry, boolean updateLastModified)
	{
		// set modified
		if (updateLastModified)
		{
			entry.setLastModifiedUtc(new Date(System.currentTimeMillis()));
		}
		
		// get update values
		ContentValues values = createContentValues(entry);
		
		SQLiteDatabase db = database.getWritableDatabase();
		// start tx
		db.beginTransaction();
		try
		{
			// update log entry
			db.update(Database.LOG_ENTRY_TABLE, values,
					Database.LOG_ENTRY_ID + " = " + entry.getId(),
					null);
			
			// remove all existing rigs
			removeRigs(entry.getId(), db);
			
			// add rigs
			addRigs(entry.getId(), entry.getRigs(), db);
			
			// set tx success
			db.setTransactionSuccessful();
		}
		finally
		{
			// end tx
			db.endTransaction();
			// close db
			db.close();
		}
	}
	
	public void deleteLogEntry(LogEntry entry)
	{
		SQLiteDatabase db = database.getWritableDatabase();
		// start tx
		db.beginTransaction();
		try
		{
			// delete rigs
			removeRigs(entry.getId(), db);
			
			// delete entry
			db.delete(
					Database.LOG_ENTRY_TABLE,
					Database.LOG_ENTRY_ID + " = " + entry.getId(),
					null);
			
			// set tx success
			db.setTransactionSuccessful();
		}
		finally
		{
			// end tx
			db.endTransaction();
			// close db
			db.close();
		}
	}
	
	private List<Rig> getRigs(int logEntryId, SQLiteDatabase db)
	{
		List<Rig> rigs = new ArrayList<Rig>();
		
		// query join table
		Cursor cursor = null;
		try
		{
			cursor = db.query(
					Database.LOG_ENTRY_RIG_TABLE,
					Database.LOG_ENTRY_RIG_ALL_COLUMNS,
					Database.LOG_ENTRY_RIG_LOG_ENTRY + " = " + logEntryId,
					null, null, null, null);
			
			// iterate over results
			while (cursor.moveToNext())
			{
				// get rig id
				int rigId = Database.getInteger(cursor, Database.LOG_ENTRY_RIG_RIG);
				
				// get rig
				Rig rig = rigRepository.getRig(rigId, db);
				rigs.add(rig);
			}
		}
		finally
		{
			// close cursor
			if (cursor != null)
			{
				cursor.close();
			}
		}
		
		return rigs;
	}
	
	private void addRigs(int logEntryId, List<Rig> rigs, SQLiteDatabase db)
	{
		ContentValues values;
		for (Rig rig: rigs)
		{
			// get values
			values = createContentValues(logEntryId, rig);
			// insert into join table
			db.insert(
					Database.LOG_ENTRY_RIG_TABLE,
					null,
					values);
		}
	}
	
	private void removeRigs(int logEntryId, SQLiteDatabase db)
	{
		// delete rows from join table
		db.delete(
				Database.LOG_ENTRY_RIG_TABLE,
				Database.LOG_ENTRY_RIG_LOG_ENTRY + " = " + logEntryId,
				null);
	}
	
	private LogEntry createLogEntry(Cursor cursor)
	{
		LogEntry entry = new LogEntry();
		
		entry.setId(Database.getInteger(cursor, Database.LOG_ENTRY_ID));
		entry.setJumpNumber(Database.getInteger(cursor, Database.LOG_ENTRY_JUMP_NUMBER));
		entry.setDate(Database.getDate(cursor, Database.LOG_ENTRY_DATE));
		entry.setAltitudeUnit(AltitudeUnit.valueOf(Database.getString(cursor, Database.LOG_ENTRY_ALTITUDE_UNIT)));
		entry.setExitAltitude(Database.getInteger(cursor, Database.LOG_ENTRY_EXIT_ALTITUDE));
		entry.setDeploymentAltitude(Database.getInteger(cursor, Database.LOG_ENTRY_DEPLOYMENT_ALTITUDE));
		entry.setFreeFallTimeSeconds(Database.getInteger(cursor, Database.LOG_ENTRY_FREEFALL_TIME));
		entry.setDiagram(Database.getBinaryData(cursor, Database.LOG_ENTRY_DIAGRAM));
		entry.setCutaway(Database.getBoolean(cursor, Database.LOG_ENTRY_CUTAWAY));
		entry.setNotes(Database.getString(cursor, Database.LOG_ENTRY_NOTES));
		entry.setLastModifiedUtc(Database.getDateTime(cursor, Database.LOG_ENTRY_LAST_MODIFIED_UTC));
		entry.setLastSignatureUtc(Database.getDateTime(cursor, Database.LOG_ENTRY_LAST_SIGNATURE_UTC));
		entry.setUniqueId(Database.getString(cursor, Database.LOG_ENTRY_UNIQUE_ID));
		
		return entry;
	}
	
	private ContentValues createContentValues(int logEntryId, Rig rig)
	{
		ContentValues values = new ContentValues();
		
		values.put(Database.LOG_ENTRY_RIG_LOG_ENTRY, logEntryId);
		values.put(Database.LOG_ENTRY_RIG_RIG, rig.getId());
		
		return values;
	}
	
	private ContentValues createContentValues(LogEntry entry)
	{
		ContentValues values = new ContentValues();
		
		values.put(Database.LOG_ENTRY_JUMP_NUMBER, entry.getJumpNumber());
		values.put(Database.LOG_ENTRY_DATE, Database.dateToString(entry.getDate()));
		if (entry.getSkydiveType() != null)
		{
			values.put(Database.LOG_ENTRY_SKYDIVE_TYPE, entry.getSkydiveType().getId());
		}
		else
		{
			values.putNull(Database.LOG_ENTRY_SKYDIVE_TYPE);
		}
		if (entry.getLocation() != null)
		{
			values.put(Database.LOG_ENTRY_LOCATION, entry.getLocation().getId());
		}
		else
		{
			values.putNull(Database.LOG_ENTRY_LOCATION);
		}
		if (entry.getAircraft() != null)
		{
			values.put(Database.LOG_ENTRY_AIRCRAFT, entry.getAircraft().getId());
		}
		else
		{
			values.putNull(Database.LOG_ENTRY_AIRCRAFT);
		}
		values.put(Database.LOG_ENTRY_ALTITUDE_UNIT, entry.getAltitudeUnit().toString());
		if (entry.getExitAltitude() != null)
		{
			values.put(Database.LOG_ENTRY_EXIT_ALTITUDE, entry.getExitAltitude());
		}
		else
		{
			values.putNull(Database.LOG_ENTRY_EXIT_ALTITUDE);
		}
		if (entry.getDeploymentAltitude() != null)
		{
			values.put(Database.LOG_ENTRY_DEPLOYMENT_ALTITUDE, entry.getDeploymentAltitude());
		}
		else
		{
			values.putNull(Database.LOG_ENTRY_DEPLOYMENT_ALTITUDE);
		}
		if (entry.getFreeFallTimeSeconds() != null)
		{
			values.put(Database.LOG_ENTRY_FREEFALL_TIME, entry.getFreeFallTimeSeconds());
		}
		else
		{
			values.putNull(Database.LOG_ENTRY_FREEFALL_TIME);
		}
		values.put(Database.LOG_ENTRY_CUTAWAY, entry.isCutaway());
		if (entry.getDiagram() != null)
		{
			values.put(Database.LOG_ENTRY_DIAGRAM, entry.getDiagram());
		}
		else
		{
			values.putNull(Database.LOG_ENTRY_DIAGRAM);
		}
		values.put(Database.LOG_ENTRY_NOTES, entry.getNotes());
		values.put(Database.LOG_ENTRY_LAST_MODIFIED_UTC, Database.dateTimeToString(entry.getLastModifiedUtc()));
		values.put(Database.LOG_ENTRY_UNIQUE_ID, entry.getUniqueId());
		if (entry.getLastSignatureUtc() != null)
		{
			values.put(Database.LOG_ENTRY_LAST_SIGNATURE_UTC, Database.dateTimeToString(entry.getLastSignatureUtc()));
		}
		else
		{
			values.putNull(Database.LOG_ENTRY_LAST_SIGNATURE_UTC);
		}
			
		return values;
	}
}